﻿BEGIN WORK;


-- COMPTE!!!   Esborra totes les dades a la base de dades

-- esborra taules en l'ordre adequat per a no violar cap referencia externa entre taules
TRUNCATE TABLE guanyadorronda CASCADE;
TRUNCATE TABLE respostaestudiant CASCADE;
TRUNCATE TABLE participacio CASCADE;
TRUNCATE TABLE inscripcio CASCADE;
TRUNCATE TABLE respostacorrecta CASCADE;
TRUNCATE TABLE ronda CASCADE;
TRUNCATE TABLE prova CASCADE;
TRUNCATE TABLE administrador CASCADE;
TRUNCATE TABLE estudiant CASCADE;
TRUNCATE TABLE responsableevents CASCADE;
TRUNCATE TABLE usuari CASCADE;

-- inicialitza les sequencies
SELECT setval('prova_idprova_seq',1);
SELECT setval('usuari_idintern_seq',1);





SET TRANSACTION READ WRITE;

--Usuari
--idIntern, tipusUsuari, idUsuari, pwd, email, estatEnSistema, tipusDocumentId, document, nom, cognom1, cognom2, dataAlta, usuariAlta, dataBaixa, usuariBaixa, dataMod, usuariMod 
INSERT INTO usuari (tipusUsuari, idUsuari, pwd, email, estatEnSistema, tipusDocumentId, document, nom, cognom1, cognom2, dataAlta, usuariAlta, dataBaixa, usuariBaixa, dataMod, usuariMod) VALUES('A','administrador','padmin','admin.eOlympics@hotmail.com','A','NIF','25649243','Eduard','Casas','Calzada',to_date('15/04/2012','dd-mm-yyyy'),null,null,null,null,null);
INSERT INTO usuari (tipusUsuari, idUsuari, pwd, email, estatEnSistema, tipusDocumentId, document, nom, cognom1, cognom2, dataAlta, usuariAlta, dataBaixa, usuariBaixa, dataMod, usuariMod) VALUES('R','responsablee','pre','responsableevents.eOlympics@hotmail.com','A','NIF','1236547659','Cristina','Oliva','Pizarro',to_date('15/04/2012','dd-mm-yyyy'),null,null,null,null,null);
INSERT INTO usuari (tipusUsuari, idUsuari, pwd, email, estatEnSistema, tipusDocumentId, document, nom, cognom1, cognom2, dataAlta, usuariAlta, dataBaixa, usuariBaixa, dataMod, usuariMod) VALUES('E','JoanMar','p1','JoanMar@hotmail.com','A','NIF','94628671','Joan','Martinez','Bosch',to_date('10/01/2012','dd-mm-yyyy'),null,null,null,null,null);
INSERT INTO usuari (tipusUsuari, idUsuari, pwd, email, estatEnSistema, tipusDocumentId, document, nom, cognom1, cognom2, dataAlta, usuariAlta, dataBaixa, usuariBaixa, dataMod, usuariMod) VALUES('E','PereRomero','p2','PereRomero@hotmail.com','A','NIF','46521394','Pere','Romero','Ter',to_date('20/04/2012','dd-mm-yyyy'),null,null,null,null,null);
INSERT INTO usuari (tipusUsuari, idUsuari, pwd, email, estatEnSistema, tipusDocumentId, document, nom, cognom1, cognom2, dataAlta, usuariAlta, dataBaixa, usuariBaixa, dataMod, usuariMod) VALUES('E','AndreuTeules','p3','AndreuTeules@hotmail.com','A','NIE','64251331','Andreu','Teules','Espinosa',to_date('20/04/2012','dd-mm-yyyy'),null,null,null,null,null);
INSERT INTO usuari (tipusUsuari, idUsuari, pwd, email, estatEnSistema, tipusDocumentId, document, nom, cognom1, cognom2, dataAlta, usuariAlta, dataBaixa, usuariBaixa, dataMod, usuariMod) VALUES('E','EvaSils','p4','EvaSils@hotmail.com','A','ALT','27964312','Eva','Sils','Carranza',to_date('10/01/2012','dd-mm-yyyy'),null,null,null,null,null);
INSERT INTO usuari (tipusUsuari, idUsuari, pwd, email, estatEnSistema, tipusDocumentId, document, nom, cognom1, cognom2, dataAlta, usuariAlta, dataBaixa, usuariBaixa, dataMod, usuariMod) VALUES('E','TomasGas','p5','TomasGas@hotmail.com','A','NIF','94625670','Tomas','Gasol','Roda',to_date('10/01/2012','dd-mm-yyyy'),null,null,null,null,null);
INSERT INTO usuari (tipusUsuari, idUsuari, pwd, email, estatEnSistema, tipusDocumentId, document, nom, cognom1, cognom2, dataAlta, usuariAlta, dataBaixa, usuariBaixa, dataMod, usuariMod) VALUES('E','LauraRod','p6','LauraRodr@hotmail.com','A','NIF','94515170','Laura','Rodrigo','Canals',to_date('10/01/2012','dd-mm-yyyy'),null,null,null,null,null);
INSERT INTO usuari (tipusUsuari, idUsuari, pwd, email, estatEnSistema, tipusDocumentId, document, nom, cognom1, cognom2, dataAlta, usuariAlta, dataBaixa, usuariBaixa, dataMod, usuariMod) VALUES('E','LaiaGrim','p7','LaiaGrim@hotmail.com','A','NIF','94585770','Laia','Grimau','Pujol',to_date('10/01/2012','dd-mm-yyyy'),null,null,null,null,null);
INSERT INTO usuari (tipusUsuari, idUsuari, pwd, email, estatEnSistema, tipusDocumentId, document, nom, cognom1, cognom2, dataAlta, usuariAlta, dataBaixa, usuariBaixa, dataMod, usuariMod) VALUES('E','AlbertCo','p8','AlbertCo@hotmail.com','A','NIF','94545662','Albert','Costa','Perez',to_date('10/01/2012','dd-mm-yyyy'),null,null,null,null,null);
INSERT INTO usuari (tipusUsuari, idUsuari, pwd, email, estatEnSistema, tipusDocumentId, document, nom, cognom1, cognom2, dataAlta, usuariAlta, dataBaixa, usuariBaixa, dataMod, usuariMod) VALUES('E','MiquelCo','p9','MiquelCo@hotmail.com','A','NIF','94535342','Miquel','Costa','Font',to_date('10/01/2012','dd-mm-yyyy'),null,null,null,null,null);
INSERT INTO usuari (tipusUsuari, idUsuari, pwd, email, estatEnSistema, tipusDocumentId, document, nom, cognom1, cognom2, dataAlta, usuariAlta, dataBaixa, usuariBaixa, dataMod, usuariMod) VALUES('E','MarcPer','p10','MarcPerCo@hotmail.com','A','NIF','94431373','Marc','Perez','Giralt',to_date('10/01/2012','dd-mm-yyyy'),null,null,null,null,null);
INSERT INTO usuari (tipusUsuari, idUsuari, pwd, email, estatEnSistema, tipusDocumentId, document, nom, cognom1, cognom2, dataAlta, usuariAlta, dataBaixa, usuariBaixa, dataMod, usuariMod) VALUES('E','MartaPuj','p11','MartaPuj@hotmail.com','A','NIF','94231541','Marta','Pujol','Alvarez',to_date('10/01/2012','dd-mm-yyyy'),null,null,null,null,null);


--estudiant
--idEstudiant, universitat
INSERT INTO estudiant VALUES (4,'UOC - Universitat Oberta de Catalunya');
INSERT INTO estudiant VALUES (5,'UOC - Universitat Oberta de Catalunya');
INSERT INTO estudiant VALUES (6,'UAB - Universitat Autonoma de Barcelona');
INSERT INTO estudiant VALUES (7,'UAB - Universitat Autonoma de Barcelona');
INSERT INTO estudiant VALUES (8,'UOC - Universitat Oberta de Catalunya');
INSERT INTO estudiant VALUES (9,'UOC - Universitat Oberta de Catalunya');
INSERT INTO estudiant VALUES (10,'UOC - Universitat Oberta de Catalunya');
INSERT INTO estudiant VALUES (11,'UAB - Universitat Autonoma de Barcelona');
INSERT INTO estudiant VALUES (12,'UAB - Universitat Autonoma de Barcelona');
INSERT INTO estudiant VALUES (13,'UAB - Universitat Autonoma de Barcelona');
INSERT INTO estudiant VALUES (14,'UAB - Universitat Autonoma de Barcelona');



--responsableevents
--idResponsable
INSERT INTO responsableevents
SELECT idintern FROM usuari WHERE tipususuari = 'R';

--administrador
--idAdministrador
INSERT INTO administrador
SELECT idintern FROM usuari WHERE tipususuari = 'A';



SELECT setval('prova_idprova_seq',1);
--prova
--idProva, descripcio, dataIniInscripcio, dataFiInscripcio, nombrePlaces, estat, dataAlta, usuariAlta, dataBaixa,  usuariBaixa, dataMod, usuariMod,   	
INSERT INTO prova (descripcio, dataIniInscripcio, dataFiInscripcio, nombrePlaces, estat, dataAlta, usuariAlta, dataBaixa, usuariBaixa, dataMod, usuariMod) VALUES ('Tecniques de desenvolupament de programari', to_date('01/04/2012','dd-mm-yyyy'),to_date('28/04/2012','dd-mm-yyyy'),11,'En competicio previa',to_date('25/03/2012','dd-mm-yyyy'),3,null,null,null,null);
INSERT INTO prova (descripcio, dataIniInscripcio, dataFiInscripcio, nombrePlaces, estat, dataAlta, usuariAlta, dataBaixa, usuariBaixa, dataMod, usuariMod) VALUES ('Xarxes', to_date('01/02/2012','dd-mm-yyyy'),to_date('25/02/2012','dd-mm-yyyy'),11,'En competicio final',to_date('15/01/2012','dd-mm-yyyy'),3,null,null,null,null);
INSERT INTO prova (descripcio, dataIniInscripcio, dataFiInscripcio, nombrePlaces, estat, dataAlta, usuariAlta, dataBaixa, usuariBaixa, dataMod, usuariMod) VALUES ('Logica', to_date('25/05/2012','dd-mm-yyyy'),to_date('25/07/2012','dd-mm-yyyy'),8,'Inscripcio',to_date('25/05/2012','dd-mm-yyyy'),3,null,null,null,null);
INSERT INTO prova (descripcio, dataIniInscripcio, dataFiInscripcio, nombrePlaces, estat, dataAlta, usuariAlta, dataBaixa, usuariBaixa, dataMod, usuariMod) VALUES ('IAG', to_date('25/05/2012','dd-mm-yyyy'),to_date('25/07/2012','dd-mm-yyyy'),8,'Inscripcio',to_date('25/05/2012','dd-mm-yyyy'),3,null,null,null,null);
INSERT INTO prova (descripcio, dataIniInscripcio, dataFiInscripcio, nombrePlaces, estat, dataAlta, usuariAlta, dataBaixa, usuariBaixa, dataMod, usuariMod) VALUES ('Matematica discreta', to_date('25/05/2012','dd-mm-yyyy'),to_date('25/07/2012','dd-mm-yyyy'),8,'Inscripcio',to_date('25/05/2012','dd-mm-yyyy'),3,null,null,null,null);



--ronda
--idProva, tipusRonda, nombrePreguntes, dataIniciRealitzacio, dataFiRealitzacio, dataAlta, usuariAlta, dataBaixa, usuariBaixa, dataMod, usuariMod  	
INSERT INTO ronda VALUES (2,'E',15,to_date('25/05/2012','dd-mm-yyyy'),to_date('25/07/2012','dd-mm-yyyy'),to_date('27/03/2012','dd-mm-yyyy'),3,null,null,null,null);
INSERT INTO ronda VALUES (2,'F',6,to_date('25/08/2012','dd-mm-yyyy'),to_date('25/10/2012','dd-mm-yyyy'),to_date('27/03/2012','dd-mm-yyyy'),3,null,null,null,null);
INSERT INTO ronda VALUES (3,'E',8,to_date('1/03/2012','dd-mm-yyyy'),to_date('27/03/2012','dd-mm-yyyy'),to_date('15/02/2012','dd-mm-yyyy'),3,null,null,null,null);
INSERT INTO ronda VALUES (3,'F',8,to_date('27/05/2012','dd-mm-yyyy'),to_date('25/07/2012','dd-mm-yyyy'),to_date('15/04/2012','dd-mm-yyyy'),3,null,null,null,null);
INSERT INTO ronda VALUES (4,'F',6,to_date('25/09/2012','dd-mm-yyyy'),to_date('25/10/2012','dd-mm-yyyy'),to_date('27/05/2012','dd-mm-yyyy'),3,null,null,null,null);
INSERT INTO ronda VALUES (5,'F',6,to_date('25/09/2012','dd-mm-yyyy'),to_date('25/10/2012','dd-mm-yyyy'),to_date('27/05/2012','dd-mm-yyyy'),3,null,null,null,null);
INSERT INTO ronda VALUES (6,'F',6,to_date('25/09/2012','dd-mm-yyyy'),to_date('25/10/2012','dd-mm-yyyy'),to_date('27/05/2012','dd-mm-yyyy'),3,null,null,null,null);


--respostacorrecta
--idProva, tipusRonda, numPregunta, opcioCorrecta, dataAlta, usuariAlta, dataBaixa, usuariBaixa, dataMod, usuariMod
INSERT INTO respostacorrecta VALUES (2,'E',1,'a',to_date('15/03/2012','dd-mm-yyyy'),3,null,null,null,null);
INSERT INTO respostacorrecta VALUES (2,'E',2,'c',to_date('15/03/2012','dd-mm-yyyy'),3,null,null,null,null);
INSERT INTO respostacorrecta VALUES (2,'E',3,'b',to_date('15/03/2012','dd-mm-yyyy'),3,null,null,null,null);
INSERT INTO respostacorrecta VALUES (2,'E',4,'d',to_date('15/03/2012','dd-mm-yyyy'),3,null,null,null,null);
INSERT INTO respostacorrecta VALUES (2,'E',5,'a',to_date('15/03/2012','dd-mm-yyyy'),3,null,null,null,null);
INSERT INTO respostacorrecta VALUES (2,'E',6,'d',to_date('15/03/2012','dd-mm-yyyy'),3,null,null,null,null);
INSERT INTO respostacorrecta VALUES (2,'E',7,'b',to_date('15/03/2012','dd-mm-yyyy'),3,null,null,null,null);
INSERT INTO respostacorrecta VALUES (2,'E',8,'a',to_date('15/03/2012','dd-mm-yyyy'),3,null,null,null,null);
INSERT INTO respostacorrecta VALUES (2,'E',9,'d',to_date('15/03/2012','dd-mm-yyyy'),3,null,null,null,null);
INSERT INTO respostacorrecta VALUES (2,'E',10,'c',to_date('15/03/2012','dd-mm-yyyy'),3,null,null,null,null);
INSERT INTO respostacorrecta VALUES (2,'E',11,'a',to_date('15/03/2012','dd-mm-yyyy'),3,null,null,null,null);
INSERT INTO respostacorrecta VALUES (2,'E',12,'c',to_date('15/03/2012','dd-mm-yyyy'),3,null,null,null,null);
INSERT INTO respostacorrecta VALUES (2,'E',13,'b',to_date('15/03/2012','dd-mm-yyyy'),3,null,null,null,null);
INSERT INTO respostacorrecta VALUES (2,'E',14,'b',to_date('15/03/2012','dd-mm-yyyy'),3,null,null,null,null);
INSERT INTO respostacorrecta VALUES (2,'E',15,'a',to_date('15/03/2012','dd-mm-yyyy'),3,null,null,null,null);

INSERT INTO respostacorrecta VALUES (2,'F',1,'a',to_date('15/04/2012','dd-mm-yyyy'),3,null,null,null,null);
INSERT INTO respostacorrecta VALUES (2,'F',2,'b',to_date('15/04/2012','dd-mm-yyyy'),3,null,null,null,null);
INSERT INTO respostacorrecta VALUES (2,'F',3,'b',to_date('15/04/2012','dd-mm-yyyy'),3,null,null,null,null);
INSERT INTO respostacorrecta VALUES (2,'F',4,'d',to_date('15/04/2012','dd-mm-yyyy'),3,null,null,null,null);
INSERT INTO respostacorrecta VALUES (2,'F',5,'c',to_date('15/04/2012','dd-mm-yyyy'),3,null,null,null,null);
INSERT INTO respostacorrecta VALUES (2,'F',6,'a',to_date('15/04/2012','dd-mm-yyyy'),3,null,null,null,null);

INSERT INTO respostacorrecta VALUES (3,'E',1,'b',to_date('15/02/2012','dd-mm-yyyy'),3,null,null,null,null);
INSERT INTO respostacorrecta VALUES (3,'E',2,'c',to_date('15/02/2012','dd-mm-yyyy'),3,null,null,null,null);
INSERT INTO respostacorrecta VALUES (3,'E',3,'c',to_date('15/02/2012','dd-mm-yyyy'),3,null,null,null,null);
INSERT INTO respostacorrecta VALUES (3,'E',4,'a',to_date('15/02/2012','dd-mm-yyyy'),3,null,null,null,null);
INSERT INTO respostacorrecta VALUES (3,'E',5,'d',to_date('15/02/2012','dd-mm-yyyy'),3,null,null,null,null);
INSERT INTO respostacorrecta VALUES (3,'E',6,'b',to_date('15/02/2012','dd-mm-yyyy'),3,null,null,null,null);
INSERT INTO respostacorrecta VALUES (3,'E',7,'c',to_date('15/02/2012','dd-mm-yyyy'),3,null,null,null,null);
INSERT INTO respostacorrecta VALUES (3,'E',8,'d',to_date('15/02/2012','dd-mm-yyyy'),3,null,null,null,null);

INSERT INTO respostacorrecta VALUES (3,'F',1,'c',to_date('15/05/2012','dd-mm-yyyy'),3,null,null,null,null);
INSERT INTO respostacorrecta VALUES (3,'F',2,'a',to_date('15/05/2012','dd-mm-yyyy'),3,null,null,null,null);
INSERT INTO respostacorrecta VALUES (3,'F',3,'d',to_date('15/05/2012','dd-mm-yyyy'),3,null,null,null,null);
INSERT INTO respostacorrecta VALUES (3,'F',4,'c',to_date('15/05/2012','dd-mm-yyyy'),3,null,null,null,null);
INSERT INTO respostacorrecta VALUES (3,'F',5,'d',to_date('15/05/2012','dd-mm-yyyy'),3,null,null,null,null);
INSERT INTO respostacorrecta VALUES (3,'F',6,'d',to_date('15/05/2012','dd-mm-yyyy'),3,null,null,null,null);
INSERT INTO respostacorrecta VALUES (3,'F',7,'a',to_date('15/05/2012','dd-mm-yyyy'),3,null,null,null,null);
INSERT INTO respostacorrecta VALUES (3,'F',8,'b',to_date('15/05/2012','dd-mm-yyyy'),3,null,null,null,null);

INSERT INTO respostacorrecta VALUES (4,'F',1,'b',to_date('15/05/2012','dd-mm-yyyy'),3,null,null,null,null);
INSERT INTO respostacorrecta VALUES (4,'F',2,'a',to_date('15/05/2012','dd-mm-yyyy'),3,null,null,null,null);
INSERT INTO respostacorrecta VALUES (4,'F',3,'b',to_date('15/05/2012','dd-mm-yyyy'),3,null,null,null,null);
INSERT INTO respostacorrecta VALUES (4,'F',4,'c',to_date('15/05/2012','dd-mm-yyyy'),3,null,null,null,null);
INSERT INTO respostacorrecta VALUES (4,'F',5,'d',to_date('15/05/2012','dd-mm-yyyy'),3,null,null,null,null);
INSERT INTO respostacorrecta VALUES (4,'F',6,'a',to_date('15/05/2012','dd-mm-yyyy'),3,null,null,null,null);

INSERT INTO respostacorrecta VALUES (5,'F',1,'b',to_date('15/05/2012','dd-mm-yyyy'),3,null,null,null,null);
INSERT INTO respostacorrecta VALUES (5,'F',2,'c',to_date('15/05/2012','dd-mm-yyyy'),3,null,null,null,null);
INSERT INTO respostacorrecta VALUES (5,'F',3,'d',to_date('15/05/2012','dd-mm-yyyy'),3,null,null,null,null);
INSERT INTO respostacorrecta VALUES (5,'F',4,'c',to_date('15/05/2012','dd-mm-yyyy'),3,null,null,null,null);
INSERT INTO respostacorrecta VALUES (5,'F',5,'a',to_date('15/05/2012','dd-mm-yyyy'),3,null,null,null,null);
INSERT INTO respostacorrecta VALUES (5,'F',6,'b',to_date('15/05/2012','dd-mm-yyyy'),3,null,null,null,null);

INSERT INTO respostacorrecta VALUES (6,'F',1,'c',to_date('15/05/2012','dd-mm-yyyy'),3,null,null,null,null);
INSERT INTO respostacorrecta VALUES (6,'F',2,'a',to_date('15/05/2012','dd-mm-yyyy'),3,null,null,null,null);
INSERT INTO respostacorrecta VALUES (6,'F',3,'d',to_date('15/05/2012','dd-mm-yyyy'),3,null,null,null,null);
INSERT INTO respostacorrecta VALUES (6,'F',4,'c',to_date('15/05/2012','dd-mm-yyyy'),3,null,null,null,null);
INSERT INTO respostacorrecta VALUES (6,'F',5,'b',to_date('15/05/2012','dd-mm-yyyy'),3,null,null,null,null);
INSERT INTO respostacorrecta VALUES (6,'F',6,'a',to_date('15/05/2012','dd-mm-yyyy'),3,null,null,null,null);



--inscripcio
--idProva, idEstudiant, dataInscripcio
INSERT INTO inscripcio VALUES (2,4,to_date('15/04/2012','dd-mm-yyyy'));
INSERT INTO inscripcio VALUES (2,5,to_date('15/04/2012','dd-mm-yyyy'));
INSERT INTO inscripcio VALUES (2,6,to_date('16/04/2012','dd-mm-yyyy'));
INSERT INTO inscripcio VALUES (2,7,to_date('15/04/2012','dd-mm-yyyy'));
INSERT INTO inscripcio VALUES (2,8,to_date('15/04/2012','dd-mm-yyyy'));
INSERT INTO inscripcio VALUES (2,9,to_date('15/04/2012','dd-mm-yyyy'));
INSERT INTO inscripcio VALUES (2,10,to_date('15/04/2012','dd-mm-yyyy'));
INSERT INTO inscripcio VALUES (2,11,to_date('15/04/2012','dd-mm-yyyy'));
INSERT INTO inscripcio VALUES (2,12,to_date('15/04/2012','dd-mm-yyyy'));
INSERT INTO inscripcio VALUES (2,13,to_date('15/04/2012','dd-mm-yyyy'));
INSERT INTO inscripcio VALUES (2,14,to_date('15/04/2012','dd-mm-yyyy'));
INSERT INTO inscripcio VALUES (3,4,to_date('15/02/2012','dd-mm-yyyy'));
INSERT INTO inscripcio VALUES (3,5,to_date('14/02/2012','dd-mm-yyyy'));
INSERT INTO inscripcio VALUES (3,6,to_date('16/02/2012','dd-mm-yyyy'));
INSERT INTO inscripcio VALUES (3,7,to_date('15/02/2012','dd-mm-yyyy'));
INSERT INTO inscripcio VALUES (3,8,to_date('15/02/2012','dd-mm-yyyy'));
INSERT INTO inscripcio VALUES (3,9,to_date('15/02/2012','dd-mm-yyyy'));
INSERT INTO inscripcio VALUES (3,10,to_date('15/02/2012','dd-mm-yyyy'));
INSERT INTO inscripcio VALUES (3,11,to_date('15/02/2012','dd-mm-yyyy'));
INSERT INTO inscripcio VALUES (3,12,to_date('15/02/2012','dd-mm-yyyy'));
INSERT INTO inscripcio VALUES (3,13,to_date('15/02/2012','dd-mm-yyyy'));
INSERT INTO inscripcio VALUES (3,14,to_date('15/02/2012','dd-mm-yyyy'));


COMMIT WORK;
